表格結構
月份 | 消費金額 |
---|---|
01/04/17 | 10000 |
01/05/17 | 10000 |
01/06/17 | 10000 |
01/07/17 | 13000 |
01/08/17 | 11000 |
01/09/17 | 12000 |
01/10/17 | 10000 |
01/11/17 | 12000 |
期望結果
日期區間 | 平均消費金額 |
---|---|
04/17 - 07/17 | 10750 |
05/17 - 08/17 | 11250 |
06/17 - 09/17 | 11750 |
07/17 - 10/17 | 12250 |
08/17 - 11/17 | 12750 |
09/17 - 12/17 | 13250 |
10/17 - 01/18 | 13750 |
11/17 - 02/18 | 14250 |
12/17 - 03/18 | 14750 |
回答
connect by level
獲取十二個月資料with CTE as (
select
--使用ADD_MONTHS-3跟-1取得連續四個月資料
TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- (level+3)),'YYYY/MM/')||'01','yyyy/mm/dd') sDate
,TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- level),'YYYY/MM/')||'01','yyyy/mm/dd') eDate
,level
from dual
--使用`connect by level`獲取十二個月資料
connect by level <=12
)
,CTE2 as (
select *
from CTE T1
left join T T2 on cast(T2."Month" as date) between T1.sDate and T1.eDate
)
select TO_CHAR(sDate,'MM/YY') || ' - ' || TO_CHAR(eDate,'MM/YY') as "Consecutive Months"
,round(avg("Consumption")) as "Avg. of 4 Months Consumption"
from CTE2
group by sDate,eDate
order by eDate
得到結果
Consecutive Months | Avg. of 4 Months Consumption |
---|---|
04/17 - 07/17 | 10750 |
05/17 - 08/17 | 11000 |
06/17 - 09/17 | 11500 |
07/17 - 10/17 | 11500 |
08/17 - 11/17 | 11250 |
09/17 - 12/17 | 11333 |
10/17 - 01/18 | 11000 |
11/17 - 02/18 | 12000 |
12/17 - 03/18 | (null) |
01/18 - 04/18 | (null) |
02/18 - 05/18 | (null) |
03/18 - 06/18 | (null) |
Test DDL:
CREATE TABLE T
("Month" timestamp, "Consumption" int)
;
INSERT ALL
INTO T ("Month", "Consumption")
VALUES ('01-Apr-2017 12:00:00 AM', 10000)
INTO T ("Month", "Consumption")
VALUES ('01-May-2017 12:00:00 AM', 10000)
INTO T ("Month", "Consumption")
VALUES ('01-Jun-2017 12:00:00 AM', 10000)
INTO T ("Month", "Consumption")
VALUES ('01-Jul-2017 12:00:00 AM', 13000)
INTO T ("Month", "Consumption")
VALUES ('01-Aug-2017 12:00:00 AM', 11000)
INTO T ("Month", "Consumption")
VALUES ('01-Sep-2017 12:00:00 AM', 12000)
INTO T ("Month", "Consumption")
VALUES ('01-Oct-2017 12:00:00 AM', 10000)
INTO T ("Month", "Consumption")
VALUES ('01-Nov-2017 12:00:00 AM', 12000)
SELECT * FROM dual
;
假如大大們有更好做法,都可以留言討論。
create table ithelp180902 (
id serial primary key
, cts timestamp not null
, consumption int not null
);
insert into ithelp180902(cts, consumption) values
('01-Apr-2017 12:00:00 AM', 10000),
('01-May-2017 12:00:00 AM', 10000),
('01-Jun-2017 12:00:00 AM', 10000),
('01-Jul-2017 12:00:00 AM', 13000),
('01-Aug-2017 12:00:00 AM', 11000),
('01-Sep-2017 12:00:00 AM', 12000),
('01-Oct-2017 12:00:00 AM', 10000),
('01-Nov-2017 12:00:00 AM', 12000);
with t1 as (
select extract(YEAR FROM cts) cyear
, extract(MONTH FROM cts) cmonth
, sum(consumption) sum_consum
from ithelp180902
group by extract(YEAR FROM cts), extract(MONTH FROM cts)
), t2 as (
select *
, round(avg(sum_consum) over(order by cyear, cmonth rows between current row and 3 following),0) avg_consum
from t1
), t3 as (
select extract(YEAR FROM n) cyear
, extract(MONTH FROM n) cmonth
, to_char(n, 'YYYY-MM')
|| ' => '
|| to_char((n + interval '3 months'), 'YYYY-MM') date_range
from generate_series('2017-04-01'::timestamp, '2018-03-01'::timestamp, '1 months') g(n)
)
select t3.date_range
, t2.avg_consum
from t2
join t3
using (cyear, cmonth)
;
date_range | avg_consum
--------------------+------------
2017-04 => 2017-07 | 10750
2017-05 => 2017-08 | 11000
2017-06 => 2017-09 | 11500
2017-07 => 2017-10 | 11500
2017-08 => 2017-11 | 11250
2017-09 => 2017-12 | 11333
2017-10 => 2018-01 | 11000
2017-11 => 2018-02 | 12000
(8 筆資料列)
驚嘆時間~
一.真方便,PGSQL可以簡單使用generate_series指定一個範圍時間,並指定格式切分成多個row
oracle還要用模擬的方式
可以參考Oracle实现POSTGRESQL的generate_series功能 - CSDN博客
select extract(YEAR FROM n) cyear
, extract(MONTH FROM n) cmonth
, to_char(n, 'YYYY-MM')
|| ' => '
|| to_char((n + interval '3 months'), 'YYYY-MM') date_range
from generate_series('2017-04-01'::timestamp, '2018-03-01'::timestamp, '1 months') g(n)
二.運用windows function : rows between current row and 3 following
簡化SQL取得連續四個月資料
select *
, round(avg(sum_consum) over(order by cyear, cmonth rows between current row and 3 following),0) avg_consum
from t1
恩??子查詢呢??
oracle能用子查詢嗎@@?
declare @Tab table(
Months date
,Consumption int
)
insert into @Tab
values('2017/4/17',10000)
,('2017/5/17',10000)
,('2017/6/17',10000)
,('2017/7/17',13000)
,('2017/8/17',11000)
,('2017/9/17',12000)
,('2017/10/17',10000)
,('2017/11/17',12000)
select Months
,(
select avg(b.Consumption)
from @Tab as b
where b.Months between a.Months and dateadd(m,3,a.Months)
) as Avg_Consumption
from @Tab as a
第一次用oracle
用線上網址測試~好像可以@_@a
select "Month"
,(
select avg("b"."Consumption")
from T "b"
where "b"."Month" between "a"."Month" and ADD_MONTHS("a"."Month",3)
) as Avg_Consumption
from T "a"
純真的人 大大
不好意思,我描述資料不好
因為他的日期不一定剛好隔一個月
是一個明細表格,所以要使用 (2017-04 => 2017-07)格式
而不是2017/11/17
可以先轉換成只有年/月/1也是可以的~
只是會多幾句轉換SQL
declare @Tab table(
Months date
,Consumption int
)
insert into @Tab
values('2017/4/17',10000)
,('2017/5/17',10000)
,('2017/6/18',10000)
,('2017/7/17',13000)
,('2017/8/17',11000)
,('2017/9/19',12000)
,('2017/10/25',10000)
,('2017/11/17',12000)
select Months
,(
select avg(b.Consumption)
from @Tab as b
where Convert(date,Convert(varchar,year(b.Months)) + '/' + Convert(varchar,month(b.Months)) + '/1') between Convert(date,Convert(varchar,year(a.Months)) + '/' + Convert(varchar,month(a.Months)) + '/1') and dateadd(m,3,Convert(date,Convert(varchar,year(a.Months)) + '/' + Convert(varchar,month(a.Months)) + '/1'))
) as Avg_Consumption
from @Tab as a
或者2012以上
select Months
,(
select avg(b.Consumption)
from @Tab as b
where DATEFROMPARTS(year(b.Months),month(b.Months),1)
between DATEFROMPARTS(year(a.Months),month(a.Months),1)
and DATEFROMPARTS(year(a.Months),month(a.Months),1)
) as Avg_Consumption
from @Tab as a